#include "supsql.h"
#include <QMessageBox>
#include <QSqlDatabase>
#include <QCoreApplication>
#include <QSqlQuery>
#include <QtDebug>

supsql * supsql::ptrSupSql = nullptr;
supsql::supsql(QObject *parent) : QObject(parent)
{

}

void supsql::init()
{
    if (QSqlDatabase::drivers().isEmpty())
        qDebug()<<"No database drivers found";

    m_db = QSqlDatabase::addDatabase("QSQLITE");
#if 1
    auto str=QCoreApplication::applicationDirPath()+"/data.db";
    qDebug()<<str;
#endif
    //m_db.setDatabaseName("E:\\QtProject\\subsystem\\supsystem\\data.db");
    m_db.setDatabaseName(str);
    if (!m_db.open())
        qDebug()<<"db do not open";
}

int supsql::getGoodCnt()
{
    QSqlQuery sql(m_db);
    sql.exec("select count(id) from goods;");
    int uicnt=0;
    while(sql.next())
    {
        uicnt=sql.value(0).toInt();
    }
    return uicnt;
}

int supsql::getUserCnt()
{
    QSqlQuery sql(m_db);
    sql.exec("select count(id) from worker;");
    int uicnt=0;
    while(sql.next())
    {
        uicnt=sql.value(0).toInt();
    }
    return uicnt;
}



QList<GoodInfo> supsql::getPageGood(int page, int uicnt)
{
    QList<GoodInfo> l;
    QSqlQuery sql(m_db);
    QString strsql = QString("select * from goods order by id limit %1 offset %2").
            arg(uicnt).arg(page*uicnt); //跳过page*uicnt条数据，选择uicnt条数据
    sql.exec(strsql);

    GoodInfo info;
    while(sql.next())
    {
        info.id= sql.value(0).toInt();
        info.name= sql.value(1).toString();
        info.kind= sql.value(2).toString();
        info.price= sql.value(3).toFloat();
        info.num= sql.value(4).toInt();
        l.push_back(info);
    }
    return l;
}

bool supsql::addGood(GoodInfo info)
{
    QSqlQuery sql(m_db);
    QString sqlstr = QString("insert into goods values(null,'%1','%2',%3,%4)").
            arg(info.name).
            arg(info.kind).
            arg(info.price).
            arg(info.num);
    return sql.exec(sqlstr);
}

bool supsql::RemoveGood(int id)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from goods where id = %1").arg(id));
}

bool supsql::ClearGoodTable()
{
    QSqlQuery sql(m_db);
    return sql.exec("delete from goods");
}

bool supsql::UpdateGoodInfo(GoodInfo info)
{
    QSqlQuery sql(m_db);
    QString sqlstr = QString("update goods set name = '%1', kind = '%2', price = '%3', num = '%4' where id =%5").
            arg(info.name).
            arg(info.kind).
            arg(info.price).
            arg(info.num).arg(info.id);
    return sql.exec(sqlstr);
}


QList<UserInfo> supsql::getPageUser(int page,int uicnt)
{
    QList<UserInfo> l;
    QSqlQuery sql(m_db);
    QString strsql = QString("select * from worker order by id limit %1 offset %2").
            arg(uicnt).arg(page*uicnt); //跳过page*uicnt条数据，选择uicnt条数据
    sql.exec(strsql);

    UserInfo info;
    while(sql.next())
    {
        info.id= sql.value(0).toInt();
        info.name= sql.value(1).toString();
        info.username= sql.value(2).toInt();
        info.password= sql.value(3).toInt();
        info.address= sql.value(4).toString();
        info.phone= sql.value(5).toString();
        l.push_back(info);
    }
    return l;
}

bool supsql::IsExist(UserInfo info)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("select *from worker where name='%1' and username =%2 ").arg(info.name).arg(info.username));

}

bool supsql::changeUserInfo(UserInfo info)
{
    QSqlQuery sql(m_db);
    QString sqlstr = QString("update worker set name = '%1', address = '%2',  phone= '%3', authority= '%4'  where username =%5").
            arg(info.name).
            arg(info.address).
            arg(info.phone).
            arg(info.Aur).arg(info.username);

    return sql.exec(sqlstr);
}

bool supsql::AddUser(UserInfo info)
{
    info.password = 666666;
    QSqlQuery sql(m_db);
    QString sqlstr = QString("insert into worker values(null,'%1',%2,%3,'%4','%5','%6')").
            arg(info.name).
            arg(info.username).
            arg(info.password).
            arg(info.address).
            arg(info.phone).
            arg(info.Aur);
        qDebug()<<info.name<<info.username<<info.address<<info.phone<<info.Aur;
    return sql.exec(sqlstr);

}

bool supsql::RemoveUser(int username)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from worker where username=%1 ").arg(username));
}

bool supsql::UpdatePwd(UserInfo info)
{
    QSqlQuery sql(m_db);
    qDebug()<<info.name<<info.username<<info.address<<info.phone<<info.Aur<<info.password;
    QString sqlstr = QString("update worker set password =%1  where username =%2 and name='%3'").
            arg(info.password).
            arg(info.username).
            arg(info.name);
    return sql.exec(sqlstr);
}
